package dao;

import model.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.DbHelper;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * -*- coding: utf-8 -*-
 *
 * @Author: LZN
 * @Date: 2023/3/12 0012 21:00
 * @Description:
 */
public class StudentDao {
    /**
     * 查询所有学生信息
     *
     * @return
     */
    public List<Student> getAllStudent() {
        List<Student> studentList = new ArrayList<>();
        Connection conn = DbHelper.getConnection();
        try {
            //编写sql语句
            String sql = "select * from student";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStudentNo(rs.getString("studentNo"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setBanji(rs.getString("banji"));
                student.setBornDate(rs.getString("bornDate"));
                student.setPhone(rs.getString("phone"));
                student.setFaculty(rs.getString("faculty"));
                student.setMajor(rs.getString("major"));
                student.setAddress(rs.getString("address"));
                //将对象添加到集合中
                studentList.add(student);
            }
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return studentList;
    }

    /**
     * 根据学号查看学生信息
     *
     * @param studentNo
     * @return
     */
    public Student getStudent(String studentNo) {
        Connection coon = DbHelper.getConnection();
        try {
            String sql = "select * from student where studentNo=?";
            PreparedStatement ps = coon.prepareStatement(sql);
            //设置参数
            ps.setString(1, studentNo);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStudentNo(rs.getString("studentNo"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setBanji(rs.getString("banji"));
                student.setBornDate(rs.getString("bornDate"));
                student.setPhone(rs.getString("phone"));
                student.setMajor(rs.getString("major"));
                student.setFaculty(rs.getString("faculty"));
                student.setAddress(rs.getString("address"));
                return student;
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 修改学生信息
     *
     * @param student
     * @return
     */
    public boolean updateStudent(Student student) {
        Connection coon = DbHelper.getConnection();
        String sql = "update student set studentNo=?,name=?,sex=?,age=?,banji=?,bornDate=?,phone=?,major=?,faculty=?,address=? where studentNo=?";
        int count = 0;
        try {
            PreparedStatement ps = coon.prepareStatement(sql);
            ps.setString(1, student.getStudentNo());
            ps.setString(2, student.getName());
            ps.setString(3, student.getSex());
            ps.setInt(4, student.getAge());
            ps.setString(5, student.getBanji());
            ps.setString(6, student.getBornDate());
            ps.setString(7, student.getPhone());
            ps.setString(8, student.getMajor());
            ps.setString(9, student.getFaculty());
            ps.setString(10, student.getAddress());
            ps.setString(11, student.getStudentNo());
            count = ps.executeUpdate();
            if (count > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 增加学生信息
     *
     * @param student
     * @return
     */
    public boolean addStudent(Student student) {
        Connection coon = DbHelper.getConnection();
        String sql = "insert into student (studentNo,name,sex,age,banji,bornDate,phone,major,faculty,address) values (?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = coon.prepareStatement(sql);
            ps.setString(1, student.getStudentNo());
            ps.setString(2, student.getName());
            ps.setString(3, student.getSex());
            ps.setInt(4, student.getAge());
            ps.setString(5, student.getBanji());
            ps.setString(6, student.getBornDate());
            ps.setString(7, student.getPhone());
            ps.setString(8, student.getMajor());
            ps.setString(9, student.getFaculty());
            ps.setString(10, student.getAddress());
            int count = ps.executeUpdate();
            if (count > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除学生信息
     *
     * @param studentNo
     * @return
     */
    public boolean deleteStudent(String studentNo) {
        String sql = "DELETE FROM student WHERE studentNo = ?"; // 删除的SQL语句，根据学号删除
        Connection conn = DbHelper.getConnection();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, studentNo);
            int count = pst.executeUpdate();
            pst.close();
            return count > 0 ? true : false; // 是否删除的判断
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通过学号查看学生信息
     *
     * @param studentNo
     * @return
     */
    public Student selectByStudentNo(String studentNo) {
        Connection coon = DbHelper.getConnection();
        Student student = null;
        try {
            String sql = "select * from student where studentNo=?";
            PreparedStatement ps = coon.prepareStatement(sql);
            //设置参数
            ps.setString(1, studentNo);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                student.setStudentNo(rs.getString("studentNo"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setBanji(rs.getString("banji"));
                student.setBornDate(rs.getString("bornDate"));
                student.setPhone(rs.getString("phone"));
                student.setMajor(rs.getString("major"));
                student.setFaculty(rs.getString("faculty"));
                student.setAddress(rs.getString("address"));
                System.out.println(student);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return student;
    }

    /**
     * 模糊查询
     */
    public List<Student> searchStudent(String studentNo, String name) throws SQLException {
        List<Student> studentList = new ArrayList<>();
        Connection coon = DbHelper.getConnection();
        Student student = null;
        try {
            String sql = "select * from student where " + studentNo + " like '%" + name + "%' order by studentNo desc";
            PreparedStatement ps = coon.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                student.setStudentNo(rs.getString("studentNo"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setSex(rs.getString("sex"));
                student.setBanji(rs.getString("banji"));
                student.setBornDate(rs.getString("bornDate"));
                student.setPhone(rs.getString("phone"));
                student.setMajor(rs.getString("major"));
                student.setFaculty(rs.getString("faculty"));
                student.setAddress(rs.getString("address"));
                studentList.add(student);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return studentList;
    }
}
